﻿USE master
Go
DROP DATABASE HospitalManagementSystem
GO

CREATE DATABASE HospitalManagementSystem
GO
USE HospitalManagementSystem
GO


CREATE TABLE NhanVien(
	MaNhanVien varchar(10) primary key,
	TenDangNhap nvarchar(100),
	MatKhau varchar(100),
	HoTen nvarchar(100),
	DiaChi nvarchar(200),
	SoDienThoai int,
	NgaySinh date,
	GioiTinh bit,
	ChucVu varchar(20)
)
GO


CREATE TABLE BenhNhan(
	MaBenhNhan varchar(10) primary key,
	HoTen nvarchar(100),
	NgaySinh date,
	DiaChi nvarchar(100),
	GioiTinh bit,
	SoDienThoai nvarchar(100),
	NgheNghiep nvarchar(100),
	NgayTaiKham datetime,
	TrangThaiKham varchar(20)
)
GO


CREATE TABLE XQuang(
	MaXQuang int identity primary key,
	MaBenhNhan varchar(10) foreign key references BenhNhan(MaBenhNhan),
	--HinhAnh image,
	KetLuan nvarchar(500),
	NgayKham datetime
)

GO
CREATE TABLE SieuAm4D(
	MaSieuAm int identity primary key,
	MaBenhNhan varchar(10) foreign key references BenhNhan(MaBenhNhan),
	Gan nvarchar(500),
	OngMatChu nvarchar(500),
	TuiMat nvarchar(500),
	Tuy nvarchar(500),
	MangPhoi nvarchar(500),
	Lach nvarchar(500),
	ThanPhai nvarchar(500),
	ThanTrai nvarchar(500),
	Bung nvarchar(500),
	BangQuang nvarchar(500),
	TuyenTienLiet nvarchar(500),
	BoPhanKhac nvarchar(500),
	KetLuan nvarchar(500),
	NgayKham datetime
)
GO



CREATE TABLE XetNghiemSinhHoaMau(
	MaXetNghiem int identity primary key,
	MaBenhNhan varchar(10) foreign key references BenhNhan(MaBenhNhan),
	Glucose float,
	Ure float,
	Creatinin float,
	AcidUric float,
	CKMB float,
	Amylase float,
	LDH float,
	ProteinTP float,
	Albumin float,
	Fibrinogen float,
	SGOT float,
	SGPT float,
	GGT float,
	BilirubinTP float,
	BilirubinTT float,
	BilirubinGP float,
	CK float,
	Cholinesterase float,
	Globulin float,
	TiLeAG float,
	TiLeNa float,
	TiLeK float,
	TiLeCL float,
	TiLeCA float,
	TiLeMagie float,
	PhosphateraseKiem float,
	Cholesterol float,
	Triglycerid float,
	HDLcho float,
	LDLcho float,
	SatHT float,
	NgayKham datetime
)
GO


CREATE TABLE NoiSoi(
	MaNoiSoi int identity primary key,
	MaBenhNhan varchar(10) foreign key references BenhNhan(MaBenhNhan),
	ThucQuan nvarchar(200),
	TamVi nvarchar(200),
	HangVi nvarchar(200),
	GocBCN nvarchar(200),
	LoViMon nvarchar(200),
	HanhTaTrang nvarchar(200),
	KetLuan nvarchar(200),
	NgayKham datetime
)
GO

CREATE TABLE ChanDoanBenh(
	MaChanDoan int identity primary key,
	MaNhanVien varchar(10) foreign key references NhanVien(MaNhanVien),
	MaBenhNhan varchar(10) foreign key references BenhNhan(MaBenhNhan),
	KetQua nvarchar(2000),
	NgayKham datetime
)
GO

CREATE TABLE DonThuoc(
	MaDonThuoc int identity primary key,
	MaNhanVien varchar(10) foreign key references NhanVien(MaNhanVien),
	MaBenhNhan varchar(10) foreign key references BenhNhan(MaBenhNhan),
	TenThuoc varchar(100),
	Sang int,
	Trua int,
	Toi int,
	SoNgay int,
	SoLuong nvarchar(20),
	GhiChu nvarchar(100),
	NgayKham datetime
)
GO

CREATE TABLE BangGiaDichVu(
	MaDichVu int identity primary key,
	TenKhoa nvarchar(100),
	TenHienThi nvarchar(100),
	TenDichVu nvarchar(100),
	GiaDichVu int
)
GO

CREATE TABLE HoSoDichVu(
	MaHoSo int identity primary key,
	MaBenhNhan varchar(10) foreign key references BenhNhan(MaBenhNhan),
	TenDichVu nvarchar(100),
	NgayKham datetime
)
GO

-------------------------------------------------------------------------------------------------------------------------------------------------------------------------

INSERT INTO NhanVien VALUES ('NV00000002','BAIKA','123456','SON LON','22 DUONG LANG',1234567890,'1990/12/12',1,'DAI KA')
INSERT INTO NhanVien VALUES ('NV00000001','BAIKA','123456','SON LON','22 DUONG LANG',1234567890,'1990/12/12',1,'DAI KA')
SELECT *FROM NhanVien
GO
DROP TABLE BenhNhan
GO
INSERT INTO BenhNhan VALUES ('BN00000002','SON LON','1990/12/12','HA NOI',1,123456,'FUCK','2013/12/25','Chờ khám')
INSERT INTO BenhNhan VALUES ('BN00000001','SON LON','1990/12/12','HA NOI',1,123456,'FUCK','2013/12/25','chờ tính tiền')
GO
DROP TABLE XQuang
GO
INSERT INTO XQuang VALUES('BN00000002','HO MOM',GETDATE())
GO
DROP TABLE SieuAm4D
GO
INSERT INTO SieuAm4D VALUES('BN00000002','HO TO','DAP NAT','HONG','NAT','RACH','DAP','NAT','PHE','TO','HONG','HONG','HONG','CHET SOM',GETDATE())
GO
DROP TABLE XetNghiemSinhHoaMau
GO
INSERT INTO XetNghiemSinhHoaMau VALUES ('BN00000002',1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,GETDATE())
GO
DROP TABLE NoiSoi
GO
INSERT INTO NoiSoi VALUES('BN00000002','DEN SI','HONG','DAP','DEN SI','LAM LONG','NHO','VO SINH',GETDATE())
GO
DROP TABLE ChanDoanBenh
GO
INSERT INTO ChanDoanBenh VALUES('NV00000002','BN00000002','CHET VI',GETDATE())
GO
DROP TABLE DonThuoc
GO
INSERT INTO DonThuoc VALUES('NV00000002','BN00000002','PANADOL',1,1,1,10,30,'UONG SAU AN',GETDATE())
GO
DROP TABLE HoSoDichVu
GO
INSERT INTO BangGiaDichVu VALUES(N'Siêu Âm',N'Siêu Âm 4D','SieuAm4D',12000000)
INSERT INTO BangGiaDichVu VALUES(N'Siêu Âm',N'Siêu Âm 3D','SieuAm4D',12000000)
INSERT INTO BangGiaDichVu VALUES(N'Khám',N'Tim','Tim',13000000)
INSERT INTO BangGiaDichVu VALUES(N'Khám',N'Phổi','Phoi',13000000)
INSERT INTO BangGiaDichVu VALUES(N'Khám',N'Gan','Gan',13000000)
GO


-------------------------------------------------------------------------------------------------------------------------------------------------------------------



CREATE TABLE DEMOAdd(
	ID int identity primary key,
	Name varchar(100)
)

SELECT * FROM DEMOAdd
INSERT INTO DEMOAdd VALUES('Son')

ALTER TABLE DEMOAdd ADD NewColumn int
GO

CREATE PROC InsertEmployee
	@MaNhanVien varchar(10),@TenDangNhap nvarchar(100),@MatKhau varchar(100),@HoTen nvarchar(100),@DiaChi nvarchar(200),@SoDienThoai int,@NgaySinh date,@GioiTinh bit,@ChucVu varchar(20)
	AS
	INSERT INTO NhanVien VALUES(@MaNhanVien,@TenDangNhap,@MatKhau,@HoTen,@DiaChi,@SoDienThoai,@NgaySinh,@GioiTinh,@ChucVu)
GO

CREATE PROC InsertUser
	@MaBenhNhan varchar(10),@Hoten nvarchar(100),@NgaySinh date,@DiaChi nvarchar(100),@GioiTinh bit,@SoDienThoai nvarchar(100),@NgheNghiep nvarchar(100)
	AS
	INSERT INTO BenhNhan VALUES(@MaBenhNhan,@Hoten,@NgaySinh,@DiaChi,@GioiTinh,@SoDienThoai,@NgheNghiep)
GO

SELECT * FROM BenhNhan WHERE MaBenhNhan like '%2%' OR HoTen like ''

Select TenHienThi from BangGiaDichVu where TenKhoa = 'Xét Nghiệm'
go

select * from BangGiaDichVu
go